﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using KingSite.SyncDB.Core.Repository;
using KingSite.SyncDB.Core.Entity;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using KingSite.SyncDB.Repository;

namespace KingSite.SyncDBManager {
    public partial class SyncJobDetailAdd : Form {
        DBRepository dbr = new DBRepository();
        SyncRepository sr = new SyncRepository();
        TBRepository tr = new TBRepository();

        DBEntity sentity;
        DBEntity dentity;
        public SyncJobDetailAdd() {
            InitializeComponent();
        }

        private void Btn_Conn_Click(object sender, EventArgs e) {
            SetDbEntity();
            if (CB_Sdb.SelectedIndex == CB_Ddb.SelectedIndex) {
                MessageBox.Show("源数据库与目标数据库不能是同一个！");
                return;
            }
            if ((DBTypeEnum)sentity.DbTypeId == DBTypeEnum.SQLSERVER && (DBTypeEnum)sentity.DbTypeId == DBTypeEnum.SQLSERVER) {
                try {
                    string sql = "select name from sysobjects where xtype='u' order by name ";
                    SqlDatabase sdb = new SqlDatabase(sentity.DbConnString);
                    DataSet ds = sdb.ExecuteDataSet(CommandType.Text, sql);
                    CB_SourceTable.DataSource = GetSourceTable(ds);

                }
                catch (Exception ex) {
                    MessageBox.Show("数据库连接失败！" + ex.Message);
                }
            }
            else {
                MessageBox.Show("暂时只支持 Sql Server 类型数据库！");
            }


        }

        private void SetDbEntity() {
            int sdbid = Convert.ToInt32(CB_Sdb.SelectedValue);
            int ddbid = Convert.ToInt32(CB_Ddb.SelectedValue);
            sentity = dbr.GetDBEntity(sdbid);
            dentity = dbr.GetDBEntity(ddbid);
        }

        private void SyncJobDetailAdd_Load(object sender, EventArgs e) {
            //初始化所有已设置数据库
            InitDb();
        }

        private void InitDb() {
            IList<DBEntity> slist = dbr.GetAllDBInfo();
            IList<DBEntity> dlist = dbr.GetAllDBInfo();
            CB_Sdb.DisplayMember = "DbName";
            CB_Sdb.ValueMember = "Id";
            CB_Sdb.DataSource = slist;
            CB_Ddb.DisplayMember = "DbName";
            CB_Ddb.ValueMember = "Id";
            CB_Ddb.DataSource = dlist;
        }

        private List<string> GetSourceTable(DataSet ds) {
            List<string> list = new List<string>();
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                foreach (DataRow dr in ds.Tables[0].Rows) {
                    list.Add(Convert.ToString(dr["name"]));
                }
            }
            return list;
        }

        private void Btn_TableColumn_Click(object sender, EventArgs e) {
            string tableName = CB_SourceTable.Text;
            try {
                DataSet ds = GetColumnsDataSetByTableName(tableName);
                dataGridView1.Columns.Clear();
                DataGridViewCheckBoxColumn newColumn = new DataGridViewCheckBoxColumn();
                newColumn.HeaderText = "选择";
                dataGridView1.Columns.Insert(0, newColumn);
                dataGridView1.DataSource = ds.Tables[0];
                CB_CheckAll.Checked = true;
            }
            catch (Exception ex) {
                MessageBox.Show("数据库连接失败！" + ex.Message);
            }
        }

        private DataSet GetColumnsDataSetByTableName(string tableName) {
            string sql = string.Format(@"
select
     c.name as [字段名],t.name as [字段类型]
     ,convert(bit,c.IsNullable)  as [可否为空]
     ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
         select name from sysindexes where indid in(
             select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
                 as [是否主键]
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [是否自增]
     ,c.Length as [占用字节] 
     ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度]
     ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
     ,ISNULL(CM.text,'') as [默认值]
     ,isnull(ETP.value,'') AS [字段描述]
     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype 
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' 
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('{0}')
and t.name not in ('timestamp','image','binary','varbinary')
order by [字段名]
", tableName);
            SqlDatabase sdb = new SqlDatabase(sentity.DbConnString);
            DataSet ds = sdb.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        private IList<ColumnEntity> ConvertToColumnEntityList() {
            IList<ColumnEntity> list = new List<ColumnEntity>();

            //循环dataGridView
            for (int i = 0; i < dataGridView1.Rows.Count -1 ; i++) {
                //设置设置每一行的选择框为选中，第一列为checkbox
                bool b = Convert.ToBoolean(dataGridView1.Rows[i].Cells[0].Value);
                if (b) {
                    ColumnEntity column = new ColumnEntity();
                    column.ColumnName = Convert.ToString(dataGridView1.Rows[i].Cells["字段名"].Value);
                    column.IsPrimaryKey = Convert.ToBoolean(dataGridView1.Rows[i].Cells["是否主键"].Value);
                    column.IsAutoIdentity = Convert.ToBoolean(dataGridView1.Rows[i].Cells["是否自增"].Value);
                    list.Add(column);
                }
            }   
            
            return list;
        }

        private void Btn_Set_Click(object sender, EventArgs e) {
            SetDbEntity();
            string tableName = CB_SourceTable.Text;
            IList<ColumnEntity> sColumnList = ConvertToColumnEntityList();
            IList<ColumnEntity> dColumnList = ConvertToColumnEntityList();
            bool b = ValidateCheck(sColumnList);
            if (!b) {
                MessageBox.Show("源列必须选择主键！");
                return;
            }
            try {
                bool IsSyncUpdate = Ckb_IsSyncUpdate.Checked;
                bool IsSyncInsert = Ckb_IsSyncInsert.Checked;
                string LastTimeStamp = GetRowMaxLastTimeStamp(tableName);
                sr.InsertSyncJob(sentity.Id, dentity.Id, tableName, tableName, sColumnList, dColumnList, IsSyncUpdate, IsSyncInsert, LastTimeStamp);
                MessageBox.Show("设置同步任务成功！");
            }
            catch (Exception ex) {
                MessageBox.Show("设置同步任务出错：" + ex.Message);
            }
        }

        /// <summary>
        /// 检查源列是否包含主键
        /// </summary>
        /// <param name="sColumnList"></param>
        /// <returns></returns>
        private bool ValidateCheck(IList<ColumnEntity> sColumnList) {
            bool b = false;
            foreach (ColumnEntity e in sColumnList) {
                if (e.IsPrimaryKey) {
                    b = true;
                    break;
                }
            }
            return b;
        }

        private string GetRowMaxLastTimeStamp(string tableName) {
            //这里要连接源数据库，查找源表的最大时间戳
            AbstractDataAccess ada = new MSSqlDataAccess(new SyncJobInfo { }, new List<SyncEntity> { });
            return ada.GetTableMaxTimeStamp(sentity.DbConnString, tableName);
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e) {
            //循环dataGridView
            for (int i = 0; i < dataGridView1.Rows.Count -1 ; i++) {
                //设置设置每一行的选择框为选中，第一列为checkbox
                dataGridView1.Rows[i].Cells[0].Value = true;
            }

        }

        private void CB_CheckAll_CheckedChanged(object sender, EventArgs e) {
            if (CB_CheckAll.Checked) {
                //循环dataGridView
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {
                    //设置设置每一行的选择框为选中，第一列为checkbox
                    dataGridView1.Rows[i].Cells[0].Value = true;
                }
            }
            else {
                //循环dataGridView
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) {
                    //设置设置每一行的选择框为选中，第一列为checkbox
                    dataGridView1.Rows[i].Cells[0].Value = false;
                }
            }
        }



    }
}
